<?php
function editUser($user_information, $user_id){
	$role_id = getRoleId($user_information[5]);
	
	$statement = "UPDATE user SET name='". $user_information[0] . 
				 "',surname='". $user_information[1] . 
				 "',street='". $user_information[2] . 
				 "',zip='". $user_information[3] . 
				 "',country='" . $user_information[4] .
				 "',role_role_id='". $role_id . 
				 "' WHERE user_id='" . $user_id . "';";
	$result = mysql_query($statement);
}

function getRegisseurInformation($regisseur_id, $arg){
	$statement = "SELECT $arg FROM regisseur WHERE regisseur_id='$regisseur_id';";
	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		return $row[$arg];
	}
}

function getRegisseurs($arg){
	$statement = "SELECT $arg FROM regisseur";
	$result = mysql_query($statement);
	return $result;
}

function getActors($arg){
	$statement = "SELECT $arg FROM actor";
	$result = mysql_query($statement);
	return $result;
}

function getRegisseurIdByName($arg){
	$statement = "SELECT regisseur_id, name FROM regisseur WHERE name = '$arg';";
	$result = mysql_query($statement);
	while($row = mysql_fetch_array($result)){
		return $row['regisseur_id'];
	}
}

function updateMovie($movie_information){
	$statement = "UPDATE movie SET " .
				 "  title='". $movie_information[1] . 
				 "',description='". $movie_information[2] . 
				 "',language='". $movie_information[3] . 
				 "',regisseur_regisseur_id='". $movie_information[4] . 
				 "' WHERE movie_id='" . $movie_information[0] . "';";
	$result = mysql_query($statement);
}

function getMovieInformation($movie_id, $arg){
	$statement = "SELECT $arg FROM view_movie WHERE movie_id='$movie_id';";
	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		return $row[$arg];
	}
}

function getAllMovies(){
	$movies = array();
	$statement = "SELECT movie_id, title, description, language, regisseur_regisseur_id FROM movie;";
	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		array_push($movies, $row['movie_id']);
		array_push($movies, $row['title']);
		array_push($movies, $row['description']);
		array_push($movies, $row['language']);
		array_push($movies, $row['regisseur_regisseur_id']);
	}
	return $movies;
}

function deleteComment($comment_id){
	$movie_id = $_SESSION['current_movie'];
	$statement = "DELETE FROM movie_comment WHERE movie_movie_id = '$movie_id' AND comment_comment_id = '$comment_id'";
	$result = mysql_query($statement);
	
	$statement = "DELETE FROM comment WHERE comment_id = '$comment_id'";
	$result = mysql_query($statement);
}

function getComments($movieId){
	$movies = array();
	$statement = "SELECT comment_id, user_id, username, comment FROM view_comment WHERE movie_id = '$movieId';";
	$result = mysql_query($statement);
	return $result;
}

function updateUser($field_name, $value){
	$user_id = $_SESSION['user_id'];
	$statement = "UPDATE user SET $field_name='$value' WHERE user_id='$user_id'";
	$result = mysql_query($statement);
}

function addComment($comment){
	$statement = "INSERT INTO comment (user_user_id, comment) VALUES ('". $_SESSION['user_id'] . "', '$comment')";
	$result = mysql_query($statement);
	
	$statement = "SELECT comment_id FROM comment WHERE comment='$comment' AND user_user_id='" . $_SESSION['user_id'] . "'";
	$result = mysql_query($statement);
	$comment_ids = array();
	while($row = mysql_fetch_array($result)){
		array_push($comment_ids, $row['comment_id']);
	}
	$comment_id = $comment_ids[ count($comment_ids) - 1 ];
	
	$statement = "INSERT INTO movie_comment (movie_movie_id, comment_comment_id) VALUES ('" . $_SESSION['current_movie'] . "', '$comment_id')";
	$result = mysql_query($statement);
}

function getRoleId($role_name){
	$statement = "SELECT role_id FROM role WHERE name = '$role_name';";
	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		return $row['role_id'];
	}
}

function deleteUser($id){
	$user_ratings  = array();
	$user_comments = array();
	
	$statement = "DELETE FROM user_offer WHERE user_user_id = '$id'";
	$result = mysql_query($statement);
	
	$statement = "DELETE FROM offer WHERE buyer_user_id = '$id'";
	$result = mysql_query($statement);
	
	$statement = "DELETE FROM message WHERE owner_user_id = '$id'";
	$result = mysql_query($statement);
	
	$statement = "DELETE FROM message WHERE receiver_user_id = '$id'";
	$result = mysql_query($statement);
	
	$statement = "SELECT comment_id FROM comment WHERE user_user_id='$id'";
	$result = mysql_query($statement);
	while($comment = mysql_fetch_array($result)){
		array_push($user_comments, $comment['comment_id']);
	}
	
	for($i = 0; $i < count($user_comments); $i++){
		$statement = "DELETE FROM movie_comment WHERE comment_comment_id = '$user_comments[$i]'";
		$result = mysql_query($statement);
	}
	
	$statement = "DELETE FROM comment WHERE user_user_id = '$id'";
	$result = mysql_query($statement);
	
	$statement = "SELECT rating_id FROM rating WHERE user_user_id = '$id'";
	$result = mysql_query($statement);
	while($rating = mysql_fetch_array($result)){
		array_push($user_ratings, $rating['rating_id']);
	}
	
	for($i = 0; $i < count($user_ratings); $i++){
		$statement = "DELETE FROM rating_movie WHERE rating_rating_id = '$user_ratings[$i]'";
		$result = mysql_query($statement);
	}
	$statement = "DELETE FROM rating WHERE user_user_id = '$id'";
	$result = mysql_query($statement);
	
	$statement = "DELETE FROM user WHERE user_id = '$id'";
	$result = mysql_query($statement);
}

function getUserId($username, $password){
	$user_id   = "";
	$user_role = "";
	$statement = "SELECT user_id, role_role_id FROM user WHERE username='$username' AND password_md5='".md5($password)."'";

	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		$user_role = $row['role_role_id'];
		$user_id   = $row['user_id'];
	}

	if($user_id == null){
		return 0;
	}else{
		$_SESSION['user_id']   = $user_id;
		$_SESSION['user_role'] = $user_role;
		return $user_id;
	}
}

function getUserId_($username){
	$user_id   = "";
	$user_role = "";
	$statement = "SELECT user_id, role_role_id FROM user WHERE username='$username'";
	
	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		return $row['user_id'];
	}
}

function deleteMovie(){
	$movie_id = $_SESSION['movie_to_del'];
	
	$statement = "DELETE FROM movie_actor WHERE movie_movie_id='$movie_id'";
	$result = mysql_query($statement);
	
	$statement = "DELETE FROM movie_genre WHERE movie_movie_id='$movie_id'";
	$result = mysql_query($statement);
	
	$statement = "DELETE FROM movie WHERE movie_id='$movie_id'";
	$result = mysql_query($statement);
}

function addOffer($movie_id, $price){
	$userId = getUserId_($_SESSION['username']);
	$statement = "INSERT INTO offer (movie_movie_id, price) ". "VALUES ($movie_id, '$price')";
	mysql_query($statement);
	$id = mysql_insert_id();
	$statement = "INSERT INTO user_offer (offer_offer_id, user_user_id) ". "VALUES ($id, $userId)";
	if(mysql_query($statement))
	return true;
	else
	return false;
}

function buyMovie($offer_id,$user_id){
	$statement = "UPDATE offer SET ordered=1 ,buyer_user_id=$user_id WHERE offer_id = $offer_id;";
	if(mysql_query($statement))
	return true;
	else
	return false;
}

function getRoles(){
	$roles = array();
	$statement = "SELECT name FROM role";
	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		array_push($roles, $row['name']);
	}
	return $roles;
}

function getRoleName($role_id){
	$statement = "SELECT name FROM role WHERE role_id='$role_id'";
	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		return $row['name'];
	}
}

function registerUser($user_information){
	// Validation
	if($user_information[0] == "" ||
	   $user_information[2] == "" ||
	   $user_information[3] == "" ||
	   $user_information[4] == "" ||
	   $user_information[5] == "" ||
	   $user_information[6] == "" ||
	   $user_information[7] == "" ){
		return 2;
	}else{
		$username = $user_information[0];
		$password = $user_information[1];
		$name 	  = $user_information[2];
		$surname  = $user_information[0];
		$street   = $user_information[4];
		$zip 	  = $user_information[5];
		$country  = $user_information[6];
	
		if($password != $user_information[7]){
			return 3;
		}
	}
	
	// Check if Username exists
	$user_exists = userExists($username);
	
	// Save user to db
	if($user_exists){
		return 4;
	}else{
		$statement = "INSERT INTO user (name, surname, street, ZIP, country, username, password_md5, role_role_id) ".
					 "VALUES ('$name', '$surname', '$street', '$zip', '$country', '$username', '$password', '2')";
		
		$result = mysql_query($statement);
	}
}

function userExists($username){
// Check if Username exists
	$statement = "SELECT user_id FROM user WHERE username='$username'";
	$result = mysql_query($statement);
	$user_exists = mysql_fetch_array($result);
	if($user_exists['user_id'] == null){
		return false;
	}else{
		return true;
	}
}

function getUserInformation($user_id, $arg){
	$statement = "SELECT $arg FROM user WHERE user_id = $user_id";
	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		return $row[$arg];
	}
}

function getUsers(){
	$users = array();
	
	$statement = "SELECT user_id, username, surname, name, ZIP, country, street, role_role_id FROM user";
	$result = mysql_query($statement);
	
	while($row = mysql_fetch_array($result)){
		array_push($users, $row['user_id']);
		array_push($users, $row['surname']);
		array_push($users, $row['name']);
		array_push($users, $row['street']);
		array_push($users, $row['ZIP']);
		array_push($users, $row['country']);
		array_push($users, $row['username']);
		array_push($users, $row['role_role_id']);
	}
	
	return $users;
}

function searchMovie($searchString){
	$statement = "SELECT * FROM view_movie WHERE title LIKE '%$searchString%';";
	$result = mysql_query($statement);
	return $result;
}

function searchActor($searchString){
	$statement = "SELECT * FROM actor WHERE name LIKE '%$searchString%';";
	$result = mysql_query($statement);
	return $result;
}

function searchRegisseur($searchString){
	$statement = "SELECT * FROM regisseur WHERE name LIKE '%$searchString%';";
	$result = mysql_query($statement);
	return $result;
}

function getOffersFromMovie($movie_id){
	$statement = "SELECT offer_id, price FROM offer WHERE movie_movie_id = ".$movie_id." AND ordered = 0;";
	$result = mysql_query($statement);
	return $result;
}

function getOffer($offer_id){
	$statement = "SELECT offer_id, price FROM offer WHERE offer_id = ".$offer_id." AND ordered = 0;";
	$result = mysql_query($statement);
	return $result;
}

function getActorsFromMovie($movie_id){
	$statement = "SELECT * FROM view_actor WHERE movie_id = ".$movie_id;
	$result = mysql_query($statement);
	return $result;
}

function getRole(){
	if(!empty($_SESSION['username'])){
		$statement = "SELECT role_role_id FROM user WHERE username = '".$_SESSION['username']."'";
		$result = mysql_query($statement);
		while($row = mysql_fetch_array($result)){
			$statement = "SELECT name FROM role WHERE role_id = ".$row['role_role_id'];
			$result2 = mysql_query($statement);
			while($row2 = mysql_fetch_array($result2)){
				return $row2['name'];
			}
		}
	}
	return "";
}

function setRating($movie, $rating, $user){
	// Insert
	$statement = "INSERT INTO rating (rating, user_user_id) ". "VALUES ('$rating', '$user')";
	mysql_query($statement);
	
	// Get rating id
	$statement = "SELECT rating_id FROM rating WHERE rating = '$rating' && user_user_id = '$user'";
	$result = mysql_query($statement);
	
	$rating_ids = array();
	while($row = mysql_fetch_array($result)){
		array_push($rating_ids, $row['rating_id']);
	}
	
	// Insert
	$rating_id = $rating_ids[ count($rating_ids) - 1 ];
	$statement = "INSERT INTO rating_movie (movie_movie_id, rating_rating_id) ". "VALUES ('$movie', '$rating_id')";
	mysql_query($statement);

}

function addRegisseur($title, $description, $language, $regisseur){
	$statement = "INSERT INTO movie (title, description, language, regisseur_regisseur_id) ". "VALUES ('$title', '$description', '$language', '$regisseur')";
	mysql_query($statement);
	return true;
}
function addActorToMovie($actor_id, $movie_id){
	$statement = "INSERT INTO movie_actor (movie_movie_id, actor_actor_id) ". "VALUES ($movie_id, $actor_id)";
	if(mysql_query($statement))
	return true;
	else
	return false;
}
?>